using B.S.XZYData.Api.Wrter.Application.Command.Process;
using B.S.XZYData.Domain.ZSHH;
using B.S.XZYData.Domain.RBAC;
using B.S.XZYData.Domain.ZSH;
using B.S.XZYData.ErrorCode;
using B.S.XZYData.Infrastructure.Interface;
using MediatR;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Transactions;
using Yitter.IdGenerator;
using System.Linq;

namespace B.S.XZYData.Api.Wrter.Application.Handler.Process
{
    /// <summary>
    /// 从Excel文件导入工序处理器
    /// </summary>
    public class ImportProcessFromFileHandler : IRequestHandler<ImportProcessFromFileCommand, ApiResult<int>>
    {
        private readonly IBaseRepository<Domain.ZSHH.Process> processRepo;
        private readonly IBaseRepository<RoleModel> roleRepo;
        private readonly IBaseRepository<Defective> defectiveRepo;
        private readonly ILogger<ImportProcessFromFileHandler> logger;

        public ImportProcessFromFileHandler(
            IBaseRepository<Domain.ZSHH.Process> processRepo,
            IBaseRepository<RoleModel> roleRepo,
            IBaseRepository<Defective> defectiveRepo,
            ILogger<ImportProcessFromFileHandler> logger)
        {
            this.processRepo = processRepo;
            this.roleRepo = roleRepo;
            this.defectiveRepo = defectiveRepo;
            this.logger = logger;
        }

        /// <summary>
        /// 从Excel文件导入工序
        /// </summary>
        /// <param name="request">命令</param>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>返回统一包装类</returns>
        public async Task<ApiResult<int>> Handle(ImportProcessFromFileCommand request, CancellationToken cancellationToken)
        {
            var res = new ApiResult<int>();

            try
            {
                if (request.File == null || request.File.Length == 0)
                {
                    res.Msg = "请选择要导入的Excel文件";
                    res.Code = ApiEnum.Fail;
                    return await System.Threading.Tasks.Task.FromResult(res);
                }

                // 检查文件扩展名
                var fileExtension = Path.GetExtension(request.File.FileName).ToLower();
                if (fileExtension != ".xlsx" && fileExtension != ".xls")
                {
                    res.Msg = "只支持.xlsx和.xls格式的Excel文件";
                    res.Code = ApiEnum.Fail;
                    return await System.Threading.Tasks.Task.FromResult(res);
                }

                // 读取Excel文件
                var processList = await ReadExcelFile(request.File);
                if (!processList.Any())
                {
                    res.Msg = "Excel文件中没有找到有效的工序数据";
                    res.Code = ApiEnum.Fail;
                    return await System.Threading.Tasks.Task.FromResult(res);
                }

                var successCount = 0;
                var failCount = 0;
                var errorMessages = new List<string>();

                using var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
                try
                {
                    foreach (var importItem in processList)
                    {
                        try
                        {
                            // 验证必填字段
                            if (string.IsNullOrWhiteSpace(importItem.ProcessName))
                            {
                                errorMessages.Add($"工序名称不能为空");
                                failCount++;
                                continue;
                            }

                            // 验证工序名称唯一性
                            var existingProcess = processRepo.GetAll()
                                .Where(x => !x.IsDeleted && x.ProcessName == importItem.ProcessName)
                                .ToList()
                                .FirstOrDefault();

                            if (existingProcess != null)
                            {
                                errorMessages.Add($"工序名称 '{importItem.ProcessName}' 已存在");
                                failCount++;
                                continue;
                            }

                            // 处理报工权限（将中文角色名称转换为角色ID）
                            var reportingPermissions = await ConvertReportingPermissionsToIds(importItem.ReportingPermissions);

                            // 处理不良品项列表（将中文描述转换为不良品项ID）
                            var defectiveItemList = await ConvertDefectiveItemListToIds(importItem.DefectiveItemList);

                            // 生成雪花ID和工序编号
                            var processId = YitIdHelper.NextId();
                            var processNumber = GenerateProcessNumber();

                            // 创建工序实体
                            var process = new Domain.ZSHH.Process
                            {
                                Id = processId,
                                ProcessNumber = processNumber,
                                ProcessName = importItem.ProcessName,
                                ReportingPermissions = reportingPermissions,
                                ReportingRatio = importItem.ReportingRatio > 0 ? importItem.ReportingRatio : 1,
                                DefectiveItemList = defectiveItemList,
                                TestField1 = importItem.TestField1,
                                TestField2 = importItem.TestField2,
                                Creator = request.Importer,
                                Updator = request.Importer
                            };

                            var result = await processRepo.CreateAsync(process);
                            if (result > 0)
                            {
                                successCount++;
                            }
                            else
                            {
                                failCount++;
                                errorMessages.Add($"工序 '{importItem.ProcessName}' 保存失败");
                            }
                        }
                        catch (Exception ex)
                        {
                            failCount++;
                            errorMessages.Add($"工序 '{importItem.ProcessName}' 处理异常：{ex.Message}");
                            logger.LogError($"导入工序 '{importItem.ProcessName}' 异常：{ex.Message}");
                        }
                    }

                    transaction.Complete();

                    res.Data = successCount;
                    if (successCount > 0)
                    {
                        res.Msg = $"导入完成，成功 {successCount} 条，失败 {failCount} 条";
                        if (errorMessages.Any())
                        {
                            res.Msg += $"，错误信息：{string.Join("; ", errorMessages.Take(5))}";
                        }
                        res.Code = ApiEnum.Success;
                    }
                    else
                    {
                        res.Msg = $"导入失败，共 {failCount} 条记录失败";
                        if (errorMessages.Any())
                        {
                            res.Msg += $"，错误信息：{string.Join("; ", errorMessages.Take(5))}";
                        }
                        res.Code = ApiEnum.Fail;
                    }

                    logger.LogInformation($"从Excel文件导入工序完成，成功 {successCount} 条，失败 {failCount} 条");
                }
                catch (Exception ex)
                {
                    logger.LogError($"导入工序事务异常：{ex.Message}");
                    res.Msg = "导入工序事务异常：" + ex.Message;
                    res.Code = ApiEnum.Error;
                }

                return await System.Threading.Tasks.Task.FromResult(res);
            }
            catch (Exception ex)
            {
                logger.LogError($"从Excel文件导入工序异常：{ex.Message}");
                res.Msg = "从Excel文件导入工序异常：" + ex.Message;
                res.Code = ApiEnum.Error;
                return await System.Threading.Tasks.Task.FromResult(res);
            }
        }

        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <param name="file">Excel文件</param>
        /// <returns>工序数据列表</returns>
        private async Task<List<ImportProcessDto>> ReadExcelFile(IFormFile file)
        {
            var processList = new List<ImportProcessDto>();

            try
            {
                using var stream = file.OpenReadStream();
                IWorkbook workbook;

                // 根据文件扩展名创建工作簿
                if (Path.GetExtension(file.FileName).ToLower() == ".xlsx")
                {
                    workbook = new XSSFWorkbook(stream);
                }
                else
                {
                    workbook = new HSSFWorkbook(stream);
                }

                var worksheet = workbook.GetSheetAt(0); // 获取第一个工作表
                if (worksheet == null)
                {
                    return processList;
                }

                // 从第二行开始读取数据（第一行是表头）
                for (int rowIndex = 1; rowIndex <= worksheet.LastRowNum; rowIndex++)
                {
                    var row = worksheet.GetRow(rowIndex);
                    if (row == null) continue;

                    // 读取单元格数据
                    var processName = GetCellValue(row.GetCell(1)); // 工序名称（B列）
                    if (string.IsNullOrWhiteSpace(processName))
                        continue; // 跳过空行

                    var reportingPermissions = GetCellValue(row.GetCell(2)); // 报工权限（C列）
                    var reportingRatio = GetCellNumericValue(row.GetCell(3)); // 报工数配比（D列）
                    var defectiveItemList = GetCellValue(row.GetCell(4)); // 不良品项列表（E列）
                    var testField1 = GetCellValue(row.GetCell(5)); // 测试字段1（F列）
                    var testField2 = GetCellValue(row.GetCell(6)); // 测试字段2（G列）

                    var importItem = new ImportProcessDto
                    {
                        ProcessName = processName,
                        ReportingPermissions = reportingPermissions,
                        ReportingRatio = reportingRatio,
                        DefectiveItemList = defectiveItemList,
                        TestField1 = testField1,
                        TestField2 = testField2
                    };

                    processList.Add(importItem);
                }

                workbook.Close();
            }
            catch (Exception ex)
            {
                logger.LogError($"读取Excel文件异常：{ex.Message}");
                throw;
            }

            return processList;
        }

        /// <summary>
        /// 获取单元格字符串值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>字符串值</returns>
        private string GetCellValue(ICell cell)
        {
            if (cell == null) return string.Empty;

            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue ?? string.Empty;
                case CellType.Numeric:
                    return cell.NumericCellValue.ToString();
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Formula:
                    try
                    {
                        return cell.StringCellValue ?? string.Empty;
                    }
                    catch
                    {
                        try
                        {
                            return cell.NumericCellValue.ToString();
                        }
                        catch
                        {
                            return string.Empty;
                        }
                    }
                default:
                    return string.Empty;
            }
        }

        /// <summary>
        /// 获取单元格数值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>整数值</returns>
        private int GetCellNumericValue(ICell cell)
        {
            if (cell == null) return 1;

            try
            {
                if (cell.CellType == CellType.Numeric)
                {
                    return (int)cell.NumericCellValue;
                }
                else if (cell.CellType == CellType.String && int.TryParse(cell.StringCellValue, out var result))
                {
                    return result;
                }
                else if (cell.CellType == CellType.Formula)
                {
                    return (int)cell.NumericCellValue;
                }
            }
            catch
            {
                // 忽略转换错误
            }

            return 1; // 默认值
        }

        /// <summary>
        /// 生成工序编号
        /// </summary>
        /// <returns>工序编号</returns>
        private string GenerateProcessNumber()
        {
            var timestamp = DateTime.Now.ToString("yyyyMMddHHmmss");
            var random = new Random();
            var randomPart = random.Next(100000, 999999).ToString();
            return timestamp + randomPart;
        }

        /// <summary>
        /// 将报工权限中文名称转换为角色ID
        /// </summary>
        /// <param name="permissionsText">报工权限文本</param>
        /// <returns>角色ID字符串</returns>
        private async Task<string> ConvertReportingPermissionsToIds(string permissionsText)
        {
            if (string.IsNullOrWhiteSpace(permissionsText))
                return "0"; // 0表示所有人

            if (permissionsText.Contains("所有人") || permissionsText.Contains("所有人"))
                return "0";

            var permissionNames = permissionsText.Split(',', StringSplitOptions.RemoveEmptyEntries)
                .Select(p => p.Trim())
                .Where(p => !string.IsNullOrWhiteSpace(p))
                .ToList();

            if (!permissionNames.Any())
                return "0";

            var roleIds = new List<string>();
            foreach (var permissionName in permissionNames)
            {
                var role = roleRepo.GetAll()
                    .Where(x => !x.IsDeleted && x.RoleName == permissionName)
                    .ToList()
                    .FirstOrDefault();

                if (role != null)
                {
                    roleIds.Add(role.Id.ToString());
                }
                else
                {
                    // 如果找不到角色，记录日志并跳过
                    logger.LogWarning($"导入工序时找不到角色：{permissionName}");
                }
            }

            return roleIds.Any() ? string.Join(",", roleIds) : "0";
        }

        /// <summary>
        /// 将不良品项中文描述转换为不良品项ID
        /// </summary>
        /// <param name="defectiveText">不良品项文本</param>
        /// <returns>不良品项ID字符串</returns>
        private async Task<string> ConvertDefectiveItemListToIds(string defectiveText)
        {
            if (string.IsNullOrWhiteSpace(defectiveText))
                return string.Empty;

            // 解析格式：数量 不良品项编号 | 描述，或者 编号 | 描述
            var defectiveItems = defectiveText.Split(',', StringSplitOptions.RemoveEmptyEntries)
                .Select(item => item.Trim())
                .Where(item => !string.IsNullOrWhiteSpace(item))
                .ToList();

            if (!defectiveItems.Any())
                return string.Empty;

            var resultIds = new List<string>();
            foreach (var item in defectiveItems)
            {
                // 解析格式：数量 编号 | 描述 或 编号 | 描述
                var parts = item.Split('|', StringSplitOptions.RemoveEmptyEntries);
                if (parts.Length >= 2)
                {
                    var quantityAndCode = parts[0].Trim();
                    var description = parts[1].Trim();

                    // 分离数量和编号
                    var quantity = 1;
                    var code = quantityAndCode;
                    
                    // 检查是否以数字开头（数量 编号的格式）
                    var firstSpaceIndex = quantityAndCode.IndexOf(' ');
                    if (firstSpaceIndex > 0 && int.TryParse(quantityAndCode.Substring(0, firstSpaceIndex), out var qty))
                    {
                        quantity = qty;
                        code = quantityAndCode.Substring(firstSpaceIndex + 1).Trim();
                    }

                    // 根据编号查找不良品项
                    var defective = defectiveRepo.GetAll()
                        .Where(x => !x.IsDeleted && x.DefectiveCode == code)
                        .ToList()
                        .FirstOrDefault();

                    if (defective != null)
                    {
                        // 存储为ID，用逗号分隔
                        resultIds.Add(defective.Id.ToString());
                    }
                    else
                    {
                        // 如果找不到，记录日志
                        logger.LogWarning($"导入工序时找不到不良品项编号：{code}");
                    }
                }
                else
                {
                    // 如果格式不正确，记录日志
                    logger.LogWarning($"导入工序时不良品项格式不正确：{item}");
                }
            }

            return string.Join(",", resultIds);
        }
    }
} 